﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; 
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Xml;
using System.Configuration;

namespace WebService
{
    public static class DataProvider
    {
        //Chuỗi kết nối cơ sở dữ liệu
        private static string _connectionstring = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        public static string ConnectionString
        {
            get { return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; }
            set { DataProvider._connectionstring = value; }
        }

        static SqlConnection connection;
        static SqlCommand command;
        static SqlDataAdapter adapter;
                
        #region Convert DaTaReader To DaTaTable        
        public static DataTable ConvertDataReaderToDataTable(IDataReader reader, bool bClose)
        {
            if (reader == null)
                return null;
            DataTable objDataTable = new DataTable();
            int intFieldCount = reader.FieldCount;
            int intCounter;
            for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
            {
                objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
            }
            objDataTable.BeginLoadData();
            object[] objValues = new object[intFieldCount];
            while (reader.Read())
            {
                reader.GetValues(objValues);
                objDataTable.LoadDataRow(objValues, true);
            }
            if (bClose)
            {
                reader.Close();
            }
            objDataTable.EndLoadData();
            return objDataTable;
        }
        #endregion

        #region Connect To Data
        public static bool Checkconnect()
        {
            try
            {
                connection = new SqlConnection(_connectionstring);
                connection.Open();
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    return true;
                }
            }
            catch (SqlException)
            {
                connection.Close();
                return false;
            }
            return false;
        }

        public static bool Checkconnect(string sqlConnectionString)
        {
            if (String.IsNullOrEmpty(sqlConnectionString))
                return false;
            try
            {
                connection = new SqlConnection(sqlConnectionString);
                connection.Open();
                if (ConnectionState.Open == connection.State)
                    return true;
            }
            catch (SqlException)
            {
                connection.Close();
                return false;
            }
            return false;

        }
        #endregion

        #region Disconnect To Data
        public static void disconnect()
        {
            if (connection != null)
                connection.Close();
        }
        #endregion

        #region Excute Query 
        public static DataTable excecuteQuery(string sqlstring)
        {
            connection = new SqlConnection(ConnectionString);
            DataTable table = new DataTable();
            connection.Open();
            if (connection.State == ConnectionState.Open)
            {
                try
                {
                    command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlstring;                    
                    SqlDataAdapter adapter = new SqlDataAdapter(command.CommandText, connection);
                    SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    adapter.Fill(table);
                    // string test = table.Rows[0]["maHocSinh"].ToString();
                    /*  SqlDataReader result = command.ExecuteReader();
                      //tb = ConvertDataReaderToDataTable( result, false );

                      DataTable dtble = result.GetSchemaTable();
                      int itemp = result.FieldCount;
                    
                      for (int i = 0; i < itemp; i++)
                      {

                          DataRow dataRow = dtble.Rows[i];
                          string columnName = dataRow["ColumnName"].ToString();
                          Type test =Type.GetType(       dataRow["Datatype"].ToString()        );
                          DataColumn column = new DataColumn(columnName, test );
                          DataTable ete = dataRow.Table;
                          tb.Columns.Add(column);
                      }
                          //  SqlDataAdapter adt = new SqlDataAdapter( sqlstring, connection );
                           // adt.Fill(tb);
                            //DataGridView t = new DataGridView();
                           // t.DataSource = tb;
                          // tb.Rows.Add( dtr );
                    //  tb.Load( result );
                      while (result.Read())
                      {
                          DataRow dataRow = tb.NewRow();
                          for (int i = 0; i < itemp; i++)
                          {
                              dataRow[i] = result.GetValue( i );
                          }
                          tb.Rows.Add(dataRow);
                      }
                      */
                    connection.Close();
                    return table;

                }
                catch (SqlException ex)
                {
                    connection.Close();
                    throw ex;
                }
            }
            connection.Close();
            return table;
        }

        public static int ExcecuteNonQuery(string sqlstring)
        {
            try
            {
                connection = new SqlConnection(_connectionstring);
                connection.Open();

            }
            catch (SqlException ex)
            {
                connection.Close();
                return 0;
            }
            int n = 0;
            if (connection.State == ConnectionState.Open)
            {
                try
                {
                    command = connection.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlstring;

                    n = command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    connection.Close();
                    return 0;
                }

            }
            if (ConnectionState.Open == connection.State)
                connection.Close();
            return n;
        }

        public static string ExecuteScalar(string sqlString)
        {
            string kq;
            try
            {
                connection = new SqlConnection(ConnectionString);
                connection.Open();
            }
            catch (SqlException ex)
            {
                connection.Close();
                throw ex;
            }

            try
            {
                command = connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = sqlString;
                kq = command.ExecuteScalar().ToString();

            }
            catch (SqlException ex)
            {
                connection.Close();
                throw ex;
            }

            connection.Close();
            return kq;
        }
        #endregion             
        
        //Đọc kết nối từ file xml
        public static string ReadConnectionString(string FileName)
        {
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(FileName);
                XmlElement root = doc.DocumentElement;
                _connectionstring = root.InnerText;
                return _connectionstring;
            }
            catch (Exception ex)
            {
                return null;
                //throw ex;
            }
        }        
    }
}
